# vip_manager.py - VIP用户管理模块

import sqlite3
from datetime import datetime
from typing import Optional, List, Tuple


class VIPManager:
    def __init__(self, db_path: str = "userlist.db"):
        """
        初始化数据库连接和表
        :param db_path: 数据库文件路径
        """
        self.db_path = db_path
        self.conn = None
        self._connect()
        self._create_table()

    def _connect(self):
        """建立数据库连接"""
        self.conn = sqlite3.connect(self.db_path, check_same_thread=False)
        self.conn.row_factory = sqlite3.Row  # 支持按列名访问结果
        print(f"已连接到数据库: {self.db_path}")

    def _create_table(self):
        """创建 viplist 表"""
        with self.conn:
            self.conn.execute('''
                CREATE TABLE IF NOT EXISTS viplist (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    qqid TEXT NOT NULL UNIQUE,
                    start_time DATETIME NOT NULL,
                    end_time DATETIME NOT NULL,
                    created_at DATETIME DEFAULT (datetime('now', 'localtime')),
                    updated_at DATETIME DEFAULT (datetime('now', 'localtime'))
                )
            ''')
            
            self.conn.execute('''
                CREATE TABLE IF NOT EXISTS user_message_history (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    qqid TEXT NOT NULL,
                    message_text TEXT NOT NULL,
                    msg_type TEXT NOT NULL CHECK(msg_type IN ('query', 'reply')), 
                    received_at DATETIME DEFAULT (datetime('now', 'localtime'))
                )
            ''')

            self.conn.execute('''
                CREATE TABLE IF NOT EXISTS group_message_history (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    group_id TEXT NOT NULL,          -- 群号码
                    sender_qqid TEXT NOT NULL,       -- 发送者QQ号
                    message_text TEXT NOT NULL,      -- 消息内容
                    nickname TEXT,                -- 用户昵称（可为空）
                    msg_type TEXT NOT NULL CHECK(msg_type IN ('query', 'reply')), 
                    sent_time DATETIME DEFAULT (datetime('now', 'localtime'))  -- 发送时间
                )
            ''')
        print("表已准备就绪。")

    def add_vip(self, qqid: str, start_time: str, end_time: str) -> bool:
        """
        添加新 VIP 用户
        :param qqid: QQ 号码
        :param start_time: 开始时间，格式 'YYYY-MM-DD HH:MM:SS'
        :param end_time: 结束时间，格式同上
        :return: 成功返回 True，否则 False（如已存在）
        """
        try:
            with self.conn:
                self.conn.execute('''
                    INSERT INTO viplist (qqid, start_time, end_time)
                    VALUES (?, ?, ?)
                ''', (qqid, start_time, end_time))
            print(f"添加成功: QQ={qqid}, 有效期至 {end_time}")
            return True
        except sqlite3.IntegrityError as e:
            if "UNIQUE constraint failed" in str(e):
                print(f"错误：QQ {qqid} 已在 VIP 列表中。")
            else:
                print(f"添加失败: {e}")
            return False

    def renew_vip(self, qqid: str, new_end_time: str) -> bool:
        """
        续期 VIP（更新结束时间）
        :param qqid: QQ 号
        :param new_end_time: 新的结束时间
        :return: 是否成功
        """
        with self.conn:
            cursor = self.conn.execute('''
                UPDATE viplist 
                SET end_time = ?, updated_at = datetime('now', 'localtime')
                WHERE qqid = ?
            ''', (new_end_time, qqid))
            if cursor.rowcount > 0:
                print(f"续期成功: QQ={qqid}，新截止时间 {new_end_time}")
                return True
            else:
                print(f"续期失败：未找到 QQ {qqid} 或无变化。")
                return False

    def is_vip(self, qqid: str) -> bool:
        """
        检查某 QQ 是否当前是有效 VIP
        :param qqid: QQ 号
        :return: 是 VIP 返回 True，否则 False
        """
        cursor = self.conn.execute('''
            SELECT 1 FROM viplist 
            WHERE qqid = ? 
              AND datetime('now', 'localtime') BETWEEN start_time AND end_time
        ''', (qqid,))
        return cursor.fetchone() is not None

    def get_active_vips(self) -> List[Tuple]:
        """
        获取所有当前有效的 VIP 用户
        :return: 列表，每个元素为 (qqid, start_time, end_time, ...)
        """
        cursor = self.conn.execute('''
            SELECT qqid, start_time, end_time, created_at 
            FROM viplist 
            WHERE datetime('now', 'localtime') BETWEEN start_time AND end_time
            ORDER BY end_time
        ''')
        return [tuple(row) for row in cursor.fetchall()]

    def get_vip_info(self, qqid: str) -> Optional[dict]:
        """
        获取某个 QQ 的 VIP 详细信息
        :param qqid: QQ 号
        :return: 字典或 None（未找到）
        """
        cursor = self.conn.execute('SELECT * FROM viplist WHERE qqid = ?', (qqid,))
        row = cursor.fetchone()
        return dict(row) if row else None

    def remove_vip(self, qqid: str) -> bool:
        """
        删除 VIP（可选：过期自动清理）
        :param qqid: QQ 号
        :return: 是否删除成功
        """
        with self.conn:
            cursor = self.conn.execute('DELETE FROM viplist WHERE qqid = ?', (qqid,))
            if cursor.rowcount > 0:
                print(f"删除成功: QQ={qqid}")
                return True
            else:
                print(f"删除失败：未找到 QQ {qqid}")
                return False
    def message_record(self, qqid: str, message_text: str, msg_type: str = 'query') -> bool:
        """
        :param qqid: 用户 QQ
        :param message_text: 消息内容
        :param msg_type: 消息类型，'query'（用户提问） 或 'reply'（机器人回复）
        :return: 是否成功
        """
        if msg_type not in ('query', 'reply'):
            raise ValueError("msg_type 必须是 'query' 或 'reply'")
        try:
            with self.conn:
                self.conn.execute('''
                    INSERT INTO user_message_history (qqid, message_text, msg_type)
                    VALUES (?, ?, ?)
                ''', (qqid, message_text.strip(), msg_type))
            return True
        except Exception as e:
            print(f"记录消息失败: {e}")
            return False
    def get_message_history(self, qqid: str, limit: int = 10) -> List[dict]:
        """
        获取某用户最近的消息历史（包含 query 和 reply）
        :param qqid: 用户 QQ
        :param limit: 最多返回几条
        :return: 列表，每项为 {'message_text': ..., 'msg_type': ..., 'received_at': ...}
        """
        cursor = self.conn.execute('''
            SELECT message_text, msg_type, received_at 
            FROM user_message_history 
            WHERE qqid = ? 
            ORDER BY received_at DESC 
            LIMIT ?
        ''', (qqid, limit))
        return [dict(row) for row in cursor.fetchall()]
    

    def record_group_message(self, group_id: str, sender_qqid: str, message_text: str, nickname: Optional[str] = None, msg_type: str = 'query') -> bool:
        """
        记录一条群消息
        :param group_id: 群号码
        :param sender_qqid: 发送者QQ号
        :param message_text: 消息内容
        :param nickname: 发送者昵称（可选）
        :return: 是否成功
        """
        try:
            with self.conn:
                self.conn.execute('''
                    INSERT INTO group_message_history (group_id, sender_qqid, message_text, nickname,msg_type)
                    VALUES (?, ?, ?, ?,?)
                ''', (group_id, sender_qqid, message_text.strip(), nickname,msg_type))
            return True
        except Exception as e:
            print(f"记录群消息失败: {e}")
            return False
    def get_group_message_history(self, group_id: str, limit: int = 20, userid: Optional[str] = None) -> List[dict]:
        """
        获取某群的消息记录（可指定用户）
        :param group_id: 群号码
        :param limit: 最多返回条数
        :param userid: 可选，指定查询的用户QQ号；若为 None，则返回全群消息
        :return: 消息记录列表
        """
        # 基础查询语句
        base_query = '''
            SELECT sender_qqid, nickname, message_text, msg_type, sent_time
            FROM group_message_history
            WHERE group_id = ?
        '''
        
        # 根据是否传入 userid 决定是否加条件
        if userid is not None:
            base_query += ' AND sender_qqid = ? '
            params = (group_id, userid, limit)
        else:
            params = (group_id, limit)

        # 添加排序和限制
        base_query += ' ORDER BY sent_time DESC LIMIT ? '

        cursor = self.conn.execute(base_query, params)
        return [dict(row) for row in cursor.fetchall()]


    def close(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.close()
            print("数据库连接已关闭。")

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()